using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using GeoAPI.Geometries;
using Npgsql;

namespace MsSqlSpatial.Utility
{
    public class ImportPostGis : StoredProcedures.IDataImportProcess
    {
        private readonly bool appendRows;

        private readonly NpgsqlCommand command;
        private readonly NpgsqlConnection connection;
        private readonly PostGisOptions postGisOptions;
        private readonly NpgsqlDataReader reader;
        private readonly ConnectionOptions sqlServerConnection;
        private readonly SqlServerOptions sqlServerOptions;

        private Dictionary<string, Type> columns;
        private ConnectionOptions postGisConnection;

        internal ImportPostGis(
            ConnectionOptions sqlServerConnection,
            ConnectionOptions postGisConnection,
            SqlServerOptions sqlServerOptions,
            PostGisOptions postGisOptions,
            bool appendRows)
        {
            this.sqlServerConnection = sqlServerConnection;
            this.postGisConnection = postGisConnection;
            this.sqlServerOptions = sqlServerOptions;
            this.postGisOptions = postGisOptions;
            this.appendRows = appendRows;

            if (string.IsNullOrEmpty(postGisOptions.Schema))
                postGisOptions.Schema = "public";

            string sql = string.Format("SELECT *, AsBinary({0}) AS MSS_WKB_GEOMETRY FROM {1}",
                                       postGisOptions.GeometryColumn,
                                       postGisOptions.Schema + "." + postGisOptions.Table);

            connection = new NpgsqlConnection(postGisConnection.GetPostGisConnectionString());
            command = new NpgsqlCommand(sql, connection);
            connection.Open();
            reader = command.ExecuteReader();
        }

        #region IDataImportProcess Members

        public void Dispose()
        {
            if (reader != null)
                reader.Dispose();
            if (command != null)
                command.Dispose();
            if (connection != null)
                connection.Dispose();
        }


        Dictionary<string, Type> StoredProcedures.IDataImportProcess.GetColumns()
        {
            return GetColumns();
        }

        void StoredProcedures.IDataImportProcess.Execute(SqlCommand command, string columnName, int srid,
                                                         out long importedRows)
        {
            importedRows = 0;
            Dictionary<string, Type> columns = GetColumns();

            while (reader.Read())
            {
                IGeometry geometry =
                    ConvertGeometry.ToGeometryFromWKB((byte[]) reader.GetValue(reader.GetOrdinal("MSS_WKB_GEOMETRY")));

                // Set values
                foreach (string column in columns.Keys)
                    command.Parameters["@" + column].Value = reader.GetValue(reader.GetOrdinal(column));
                command.Parameters["@" + columnName].Value = ConvertGeometry.ToSqlBytes(geometry, srid).Value;

                // Insert row
                command.ExecuteNonQuery();
                importedRows++;

                if (ItemImported != null)
                    ItemImported(this, new StoredProcedures.ImportEventArgs(importedRows, 0));
            }
        }

        public event EventHandler<StoredProcedures.ImportEventArgs> ItemImported;

        #endregion

        internal TaskStats Execute()
        {
            TaskStats result;

            StoredProcedures.DataImportProcess(
                this,
                sqlServerOptions.Schema,
                sqlServerOptions.Table,
                sqlServerOptions.Column,
                sqlServerOptions.SRID,
                sqlServerOptions.Subtype,
                sqlServerConnection.GetSqlServerConnectionString(),
                appendRows,
                out result.RowsProcessed,
                out result.ElapsedTime);

            return result;
        }

        private Dictionary<string, Type> GetColumns()
        {
            if (columns == null)
            {
                columns = new Dictionary<string, Type>();
                for (int i = 0; i < reader.FieldCount - 1; i++)
                {
                    string name = reader.GetName(i);
                    if (name != postGisOptions.GeometryColumn && reader.GetDataTypeName(i) != "geometry")
                        columns[name] = reader.GetFieldType(i);
                }
            }
            return columns;
        }
    }
}